SQL Server 2017 brings several improvements to us. Sometimes a simple new field in a DMV can turn possible interesting new functionalities. That’s what happens with differential backup in SQL Server 2017.

The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages were changed since the last full backup. Basically, it’s the information kept by DCM (Differential Change Map) that until now was very difficult to check.. We could use DBCC Page for this, however it’s not possible to use the information in automated scripts.

What are the possibilities with this new field ? We are now able to check how many extents have changed since last full backup and decide if a full backup is really needed or we can live with a differential backup, achieving smarter backup plans.

Change our full backup jobs to first check this field and decide if the backup will be full or differential can save space and maintenance time with databases that aren’t updated so often.

Let’s do a simple walkthrough to illustrate this. I will use AdventureWorks2016 database for this demonstration, you can download this sample database here: https://www.microsoft.com/en-us/download/details.aspx?id=49502

1. Change the recovery model and take the first full backup:

   1 2 3 4 5 6 7

   use AdventureWorks2016CTP3 go Alter database AdventureWorks2016CTP3 set recovery full go — The initial full backup backup database adventureworks2016ctp3 to disk=‘c:\backups\newCopy.bak’

2. Check the modified pages

   1 2 3 4

   — Identifying the amount of changes Select file_id,total_page_count, modified_extent_page_count,        (100 * modified_extent_page_count)/total_page_count [percent] from sys.dm_db_file_space_usage

    

3. Let’s do a lot of updates:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   — Let’s do a lot of updates update sales.SalesOrderDetail set orderqty=orderqty+1 update sales.ordertracking set eventdetails=eventdetails + ‘x’ update production.TransactionHistory set quantity=quantity +1 update production.TransactionHistoryArchive set quantity=quantity +1 update sales.SalesOrderDetail_ondisk set OrderQty= OrderQty +1 update person.person set emailpromotion=1

4. Let’s check the modified pages again and run a simple backup script. There are not enough modified pages, the script will choose a differential backup.

   1 2 3 4 5 6

   — Identifying the amount of changes – again SELECT file_id,        total_page_count,        modified_extent_page_count,        ( 100 * modified_extent_page_count ) / total_page_count [percent] FROM   sys.dm_db_file_space_usage

   

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   — Our backup script will be like this DECLARE @changes NUMERIC(15, 3) SELECT @changes = ( 100 * Sum(modified_extent_page_count) /                     Sum(total_page_count) ) FROM   sys.dm_db_file_space_usage IF @changes > 65   BEGIN       — Too many changes, do a full backup       BACKUP DATABASE adventureworks2016ctp3 TO DISK=‘c:\backups\newCopy.bak’   END ELSE   BEGIN       — Too few changes, do a differential backup       BACKUP DATABASE adventureworks2016ctp3 TO DISK=‘c:\backups\newCopy.bak’       WITH       differential   END

   

5. A lot more updates

   1 2 3 4 5 6 7 8 9 10 11 12 13

   — Let’s do a lot more updates UPDATE sales.customerpii SET    lastname = lastname + ‘x’ UPDATE production.workorderrouting SET    actualcost = actualcost + 1 UPDATE sales.salesorderheader SET    subtotal = subtotal + 1 UPDATE sales.salesorder_json SET    subtotal = subtotal + 1 UPDATE person.address SET    addressline1 = addressline1 + ‘x’ UPDATE person.emailaddress SET    emailaddress = emailaddress + ‘x’

6. Again, check the modified pages and our backup script. Now there are a lot of modified extents, the script will choose a full backup.

   1 2 3 4 5 6

   — Identifying the amount of changes – again SELECT file_id,        total_page_count,        modified_extent_page_count,        ( 100 * modified_extent_page_count ) / total_page_count [percent] FROM   sys.dm_db_file_space_usage

   

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   — Our backup script will be like this DECLARE @changes NUMERIC(15, 3) SELECT @changes = ( 100 * Sum(modified_extent_page_count) /                     Sum(total_page_count) ) FROM   sys.dm_db_file_space_usage IF @changes > 65   BEGIN       — Too many changes, do a full backup       BACKUP DATABASE adventureworks2016ctp3 TO DISK=‘c:\backups\newCopy.bak’   END ELSE   BEGIN       — Too few changes, do a differential backup       BACKUP DATABASE adventureworks2016ctp3 TO DISK=‘c:\backups\newCopy.bak’       WITH differential   END

   

7. Check the modified pages again. The full backup cleaned the DCM.

   1 2 3 4 5 6

   — Identifying the amount of changes – again SELECT file_id,        total_page_count,        modified_extent_page_count,        ( 100 * modified_extent_page_count ) / total_page_count [percent] FROM   sys.dm_db_file_space_usage